package com.ruge.tool.jdbc;

import cn.hutool.core.text.StrFormatter;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONObject;
import com.ruge.tool.json.JsonTool;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * description:
 * create time at 2024/6/13 20:38
 *
 * @author alice.ruge
 * @since 0.0.3
 */
@Slf4j
public class JdbcTool {

    public static List<JSONObject> execQuerySql(Connection conn, String sql, String... params) throws SQLException {
        sql = StrFormatter.format(sql, params);
        log.info("sql:");
        log.info(sql);
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        List<JSONObject> list = resultSet2JsonList(resultSet);
        log.info("result:");
        log.info(JsonTool.getObjToJson(list));
        return list;
    }

    public static JSONObject execQueryOneSql(Connection conn, String sql, String... params) throws SQLException {
        sql = StrFormatter.format(sql, params);
        log.info("sql:");
        log.info(sql);
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        JSONObject jsonObject = resultSet2JsonOne(resultSet);
        log.info("result:");
        log.info(JsonTool.getObjToJson(jsonObject));
        return jsonObject;
    }

    public static JSONObject resultSet2JsonOne(ResultSet resultSet) throws SQLException {
        JSONObject jsonObject = new JSONObject();
        while (resultSet.next()) {
            int total_rows = resultSet.getMetaData().getColumnCount();
            for (int i = 0; i < total_rows; i++) {
                String key = resultSet.getMetaData().getColumnLabel(i + 1).toLowerCase();
                key = StrUtil.toCamelCase(key);
                Object value = resultSet.getObject(i + 1);
                jsonObject.put(key, value);
            }
        }
        return jsonObject;
    }

    public static List<JSONObject> resultSet2JsonList(ResultSet resultSet) throws SQLException {
        List<JSONObject> jsonArray = new ArrayList<>();
        while (resultSet.next()) {
            int total_rows = resultSet.getMetaData().getColumnCount();
            JSONObject obj = new JSONObject();
            for (int i = 0; i < total_rows; i++) {
                String key = resultSet.getMetaData().getColumnLabel(i + 1).toLowerCase();
                key = StrUtil.toCamelCase(key);
                Object value = resultSet.getObject(i + 1);
                obj.put(key, value);
            }
            jsonArray.add(obj);
        }
        return jsonArray;
    }
}
